﻿using MongoDB.Bson;
using MongoDB.Driver;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace DataBaseTool
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }
       // private string server;
       // private string database;
       // private string user;
       // private string pass;
       // private string ext;

        private void button2_Click(object sender, EventArgs e)
        {

           string server = textBox1.Text;
           string database = textBox5.Text;
           string user = textBox2.Text;
           string pass = textBox3.Text;
           string ext = textBox4.Text;

            if (string.IsNullOrEmpty(server) || string.IsNullOrEmpty(database)||string.IsNullOrEmpty(user)|| string.IsNullOrEmpty(pass))
            {
                return;
            }


            string connectionStringTest = @"server=" + server + ";database=" + database + ";user id=" + user + ";password=" + pass + ";" + ext;

            SqlConnection con = getConnection(connectionStringTest);
            
            string sql = "select TABLE_NAME,TABLE_TYPE,TABLE_CATALOG,TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';";

            DataTable dt = Select(sql, con);

            connectMongoDB();

            for (int i = 0; i < dt.Rows.Count; i++)
            {

                string tb_name = dt.Rows[i]["TABLE_NAME"].ToString();


                sql = "select * from "+tb_name;

                DataTable dt_1 = Select(sql, con);

                Console.WriteLine(tb_name);

                sql = "select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS t where t.TABLE_NAME = '" + tb_name + "';";


                DataTable dt2 = Select(sql, con);

                
                

                for (int k = 0; k <dt_1.Rows.Count; k++)
                {
                    BsonDocument doc = new BsonDocument();

                    DataRow dr = dt_1.Rows[k];
                    for (int j = 0; j < dt2.Rows.Count; j++)
                    {
                        string col_name = dt2.Rows[j]["COLUMN_NAME"].ToString();

                        string col_type = dt2.Rows[j]["DATA_TYPE"].ToString();

                        if (string.IsNullOrEmpty(dr[col_name].ToString()))
                        {
                            doc.Add(col_name,"");
                            continue;
                        }

                        switch (col_type)
                        {
                            case "int":
                                doc.Add(col_name,int.Parse(dr[col_name].ToString()));
                                break;
                            case "varchar":
                                doc.Add(col_name,dr[col_name].ToString());
                                 
                                break;
                            case "bit":
                                doc.Add(col_name,Boolean.Parse(dr[col_name].ToString()));
                                
                                break;
                            case "datetime":
                                doc.Add(col_name,dr[col_name].ToString());

                                break;
                            case "decimal":
                                doc.Add(col_name,dr[col_name].ToString());
                                
                                break;
                            case "float":
                                doc.Add(col_name,dr[col_name].ToString());
                                
                                break;
                            case "image":
                                byte[] b = (byte[])dr[col_name];
                                doc.Add(col_name,b);
                                
                                break;
                            case "money":
                                doc.Add(col_name, dr[col_name].ToString());
                                break;
                            case "ntext":
                                doc.Add(col_name, dr[col_name].ToString());
                                break;
                            case "nvarchar":
                                doc.Add(col_name, dr[col_name].ToString());
                                break;
                            case "smalldatetime":
                                doc.Add(col_name, DateTime.Parse(dr[col_name].ToString()));
                                break;
                            case "smallint":
                                doc.Add(col_name, int.Parse(dr[col_name].ToString()));
                                break;
                            case "text":
                                doc.Add(col_name, dr[col_name].ToString());
                                break;
                            case "bigint":
                                doc.Add(col_name, int.Parse(dr[col_name].ToString()));
                                break;
                            case "char":
                                doc.Add(col_name, dr[col_name].ToString());
                                break;
                            case "nchar":
                                doc.Add(col_name, dr[col_name].ToString());
                                break;
                            case "numeric":
                                doc.Add(col_name, int.Parse(dr[col_name].ToString()));
                                break;
                        }

                        
                        

                    }
                    
                    mongo_insertOne(tb_name, doc);

                }


                


                sql = "SELECT OBJECT_NAME(sysobjects.parent_obj) tableName, syscolumns.name AS columnName FROM sysobjects INNER JOIN sysconstraints ON sysobjects.xtype in('PK')   AND sysobjects.id = sysconstraints.constid inner JOIN syscolumns ON sysconstraints.id = syscolumns.id ";

                DataTable dt3 = Select(sql, con);


            }


            
            





            }



        public  Type SqlType2CsharpType(SqlDbType sqlType)
        {
            switch (sqlType)
            {
                case SqlDbType.BigInt:
                    return typeof(Int64);
                case SqlDbType.Binary:
                    return typeof(Object);
                case SqlDbType.Bit:
                    return typeof(Boolean);
                case SqlDbType.Char:
                    return typeof(String);
                case SqlDbType.DateTime:
                    return typeof(DateTime);
                case SqlDbType.Decimal:
                    return typeof(Decimal);
                case SqlDbType.Float:
                    return typeof(Double);
                case SqlDbType.Image:
                    return typeof(Object);
                case SqlDbType.Int:
                    return typeof(Int32);
                case SqlDbType.Money:
                    return typeof(Decimal);
                case SqlDbType.NChar:
                    return typeof(String);
                case SqlDbType.NText:
                    return typeof(String);
                case SqlDbType.NVarChar:
                    return typeof(String);
                case SqlDbType.Real:
                    return typeof(Single);
                case SqlDbType.SmallDateTime:
                    return typeof(DateTime);
                case SqlDbType.SmallInt:
                    return typeof(Int16);
                case SqlDbType.SmallMoney:
                    return typeof(Decimal);
                case SqlDbType.Text:
                    return typeof(String);
                case SqlDbType.Timestamp:
                    return typeof(DateTime);
                case SqlDbType.TinyInt:
                    return typeof(Byte);
                case SqlDbType.Udt://自定义的数据类型
                    return typeof(Object);
                case SqlDbType.UniqueIdentifier:
                    return typeof(Object);
                case SqlDbType.VarBinary:
                    return typeof(Object);
                case SqlDbType.VarChar:
                    return typeof(String);
                case SqlDbType.Variant:
                    return typeof(Object);
                case SqlDbType.Xml:
                    return typeof(Object);
                default:
                    return null;
            }
        }

        // sql server数据类型（如：varchar）
        // 转换为SqlDbType类型
        public  string SqlTypeString2MongoDBType(string sqlTypeString)
        {
            string type = "string";

            switch (sqlTypeString)
            {
                case "int":
                    type = "integer";
                    break;
                case "varchar":
                    type = "string";
                    break;
                case "bit":
                    type = "boolean";
                    break;
                case "datetime":
                    type = "date";
                    break;
                case "decimal":
                    type = "integer";
                    break;
                case "float":
                    type = "double";
                    break;
                case "image":
                    type = "binary";
                    break;
                case "money":
                    type = "string";
                    break;
                case "ntext":
                    type = "string";
                    break;
                case "nvarchar":
                    type = "string";
                    break;
                case "smalldatetime":
                    type = "date";
                    break;
                case "smallint":
                    type = "integer";
                    break;
                case "text":
                    type = "string";
                    break;
                case "bigint":
                    type = "double";
                    break;
                case "binary":
                    type = "binary";
                    break;
                case "char":
                    type = "string";
                    break;
                case "nchar":
                    type = "string";
                    break;
                case "numeric":
                    type = "integer";
                    break;
            }
            return type;
        }






        public DataTable Select(string sql, SqlConnection conn)
        {
            SqlCommand cmd = null;
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);

                sda.Fill(dt);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            try
            {
                conn.Close();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return dt;
        }



        private SqlConnection getConnection(string connectionStringTest)
        {



            SqlConnection conn = new SqlConnection(connectionStringTest);

            return conn;


        }

        private void button1_Click(object sender, EventArgs e)
        {

            string   server = textBox1.Text;
            string database = textBox5.Text;
            string user = textBox2.Text;
            string pass = textBox3.Text;
            string ext = textBox4.Text;

            if (string.IsNullOrEmpty(server) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(user) || string.IsNullOrEmpty(pass))
            {
                return;
            }


            string connectionStringTest = @"server=" + server + ";database=" + database + ";user id=" + user + ";password=" + pass + ";" + ext;

            SqlConnection con = getConnection(connectionStringTest);

            try
            {
                con.Open();
                MessageBox.Show("success");
            }
            catch(Exception ex)
            {
                MessageBox.Show("failed:"+ex.Message.ToString());
            }

            try
            {
                con.Close();
                con.Dispose();
                con = null;
            }
            catch
            {

            }



        }

        private void button8_Click(object sender, EventArgs e)
        {
            MyMongoHelper myMongoHelper = new MyMongoHelper();

            string host = textBox7.Text;
            int port=27017;
            try
            {
                port = int.Parse(textBox6.Text);
            }
            catch
            {
            }
            string data_base = textBox11.Text;
            string user_name=textBox12.Text;
            string pass_word=textBox9.Text;

            if (string.IsNullOrEmpty(host)|| string.IsNullOrEmpty(port.ToString())|| string.IsNullOrEmpty(data_base)|| string.IsNullOrEmpty(user_name))
            {
                return;
            }

            myMongoHelper.host = host;
            myMongoHelper.port = port;
            myMongoHelper.data_base = data_base;
            myMongoHelper.user_name = user_name;
            myMongoHelper.pass_word = pass_word;

            MongoClient mc=myMongoHelper.getMyClient();
            var db=mc.GetDatabase(data_base);

            BsonDocument doc = new BsonDocument();


            long ct=myMongoHelper.count("t1", doc);
            if (ct > 0)
            {

            }







                }

        private void mongo_insertMany(string collectionName, List<BsonDocument> docs)
        {
            MyMongoHelper myMongoHelper = new MyMongoHelper();

            string host = textBox7.Text;
            int port = 27017;
            try
            {
                port = int.Parse(textBox6.Text);
            }
            catch
            {
            }
            string data_base = textBox11.Text;
            string user_name = textBox12.Text;
            string pass_word = textBox9.Text;

            if (string.IsNullOrEmpty(host) || string.IsNullOrEmpty(port.ToString()) || string.IsNullOrEmpty(data_base) || string.IsNullOrEmpty(user_name))
            {
                return;
            }

            myMongoHelper.host = host;
            myMongoHelper.port = port;
            myMongoHelper.data_base = data_base;
            myMongoHelper.user_name = user_name;
            myMongoHelper.pass_word = pass_word;

            myMongoHelper.insertMany(collectionName, docs);
            
        }


        MyMongoHelper myMongoHelper = new MyMongoHelper();

        MongoClient client = null;

      IMongoDatabase database = null;

        private void connectMongoDB()
        {

            string host = textBox7.Text;
            int port = 27017;
            try
            {
                port = int.Parse(textBox6.Text);
            }
            catch
            {
            }
            string data_base = textBox11.Text;
            string user_name = textBox12.Text;
            string pass_word = textBox9.Text;

            
            string connectionString = "mongodb://" + user_name + ":" + pass_word + "@" + host + ":" + port + "/" + data_base;
            //指定安全证书
            MongoCredential credential = MongoCredential.CreateCredential(data_base, user_name, pass_word);

            client = new MongoClient(connectionString);


           

            myMongoHelper.host = host;
            myMongoHelper.port = port;
            myMongoHelper.data_base = data_base;
            myMongoHelper.user_name = user_name;
            myMongoHelper.pass_word = pass_word;



             database = client.GetDatabase(data_base);
            


        }


        private void mongo_insertOne(string collectionName, BsonDocument doc)
        {
            
            //建立collection
            var collection = database.GetCollection<BsonDocument>(collectionName);

            collection.InsertOne(doc);
            

        }



    }
}
